package com.delete.sql;

import java.util.Date;

import org.apache.commons.lang3.StringUtils;

import com.kevin.enums.DBType;
import com.kevin.exceptions.CreaterException;
import com.kevin.sql.SqlFormat;

public class SqlCreateForPager {
	/// <summary>
    /// 首页查询
    /// </summary>
    private final static String TOP1PAGER = "select %s from %s where %s";
    /// <summary>
    /// 最后一页查询
    /// </summary>
    private final static String TOP2PAGER = "select top %s from (select %s from %s where %s) v %s";
    /// <summary>
    /// 前半段分页查询
    /// </summary>
    private final static String TOP3PAGER = "select top %s %s from (select top %s * from (select top %s * from %s where %s) v %s) v %s";
    /// <summary>
    /// 后半段分页查询（即倒过来查询）
    /// </summary>
    private final static String TOP4PAGER = "select %s from (select top %s * from (select top %s * from (select top %s * from %s where %s) v %s) v %s) v %s";
    /// <summary>
    /// MSSQL、Oracle的行号分页
    /// </summary>
    private final static String ROWNUMBERPAGER = "select * from(select row_number() over (%s) cyqrownum,%s from %s t %s)%s where cyqrownum between %s and %s";
    /// <summary>
    /// 临时表分页（不带自增加序列）
    /// </summary>
    private final static String TEMPTABLEPAGER = "select top %s,cyqrownum=identity(int) into #tmp%s from %s where %s select top %s * from #tmp%s where cyqrownum between %s and %s %s drop table #tmp%s";
    /// <summary>
    /// 临时表分页（带自增加序列）
    /// </summary>
    private final static String TEMPTABLEPAGERWITHIDENTITY = "select top %s cast(%s as int) cyqrowid,cyqrownum=identity(int) into #tmp%s from %s where %s select top %s %s from #tmp%s left join %s on %s=cyqrowid where cyqrownum between %s and %s %s drop table #tmp%s";

	
	@SuppressWarnings("incomplete-switch")
	public static String getSql(DBType dbType, int pageIndex, int pageSize, Object objWhere, String tableName, int rowCount, String columns, String primaryKey, boolean primaryKeyIsIdentity)
    {
        if (StringUtils.isEmpty(columns))
        {
            columns = "*";
        }
        pageIndex = pageIndex == 0 ? 1 : pageIndex;
        String where = SqlFormat.getFieldRowSql(objWhere);
        if (StringUtils.isEmpty(where))
        {
            where = "1=1";
        }
        if (pageSize == 0)
        {
            return String.format(TOP1PAGER, columns, tableName, where);
        }
        if (rowCount > 0)//分页查询。
        {
        	//按照主键进行排序
            where = SqlCreate.addOrderBy(where, primaryKey);
        }
        int topN = pageIndex * pageSize;//Top N 最大数
        int max = (pageIndex - 1) * pageSize;
        int rowStart = (pageIndex - 1) * pageSize + 1;
        int rowEnd = rowStart + pageSize - 1;
        String orderBy = StringUtils.EMPTY;
        if (pageIndex == 1 && dbType != DBType.ORACLE)//第一页（oracle时 rownum 在排序条件为非数字时，和row_number()的不一样，会导致结果差异，所以分页统一用row_number()。）
        {
            switch (dbType)
            {
                case ACCESS:
                case MSSQL:
                case SYBASE:
                    return String.format(TOP1PAGER, "top " + pageSize + " " + columns, tableName, where);
                //case DBType.Oracle:
                //    return String.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
                case SQLITE:
                case MYSQL:
                    return String.format(TOP1PAGER, columns, tableName, where + " limit " + pageSize);
            }
        }
        else
        {

            switch (dbType)
            {
                case ACCESS:
                case MSSQL:
                case SYBASE:
                    int leftNum = rowCount % pageSize;
                    int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;//页数
                    if (pageIndex == pageCount && dbType != DBType.SYBASE) // 最后一页Sybase 不支持双Top order by
                    {
                        return String.format(TOP2PAGER, pageSize+" "+columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey));//反序
                    }
                    if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段，反转查询
                    {
                        orderBy = GetOrderBy(where, false, primaryKey);
                        where = ReverseOrderBy(where, primaryKey);//事先反转一次。
                        topN = rowCount - max;//取后面的
                        int rowStartTemp = rowCount - rowEnd;
                        rowEnd = rowCount - rowStart;
                        rowStart = rowStartTemp;
                    }
                    break;

            }
        }

        DBType tempType = dbType;
        boolean dowhile = false;
        do {
        	dowhile = false;
        	switch (tempType)
            {
                case MSSQL:
                case ORACLE:
//                    if (version.startsWith("08"))
//                    {
//                    	dowhile = true;
//                    	tempType =  DBType.SYBASE;
//                        break;
//                        // goto top3;//sql 2000
//                    }
                    int index = tableName.lastIndexOf(')');
                    if (index > 0)
                    {
                        tableName = tableName.substring(0, index + 1);
                    }
                    String v = dbType == DBType.ORACLE ? "" : " v";
                    String onlyWhere = "where " + SqlCreate.removeOrderBy(where);
                    onlyWhere = SqlFormat.whereOneEqOne(onlyWhere);
                    return String.format(ROWNUMBERPAGER, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd);
                case SYBASE:
                    if (primaryKeyIsIdentity)
                    {
                        boolean isOk = columns == "*";
                        if (!isOk)
                        {
                            String kv = SqlFormat.notKeyword(primaryKey);
                            String[] items = StringUtils.split(columns, ',');
                            for (String item : items)
                            {                       	
                                if (SqlFormat.notKeyword(item).compareToIgnoreCase(kv) == 0)
                                {
                                    isOk = true;
                                    break;
                                }
                            }
                        }
                        else
                        {
                            columns = "t.*";
                            index = tableName.lastIndexOf(')');
                            if (index > 0)
                            {
                                tableName = tableName.substring(0, index + 1);
                            }
                            tableName += " t ";
                        }
                        if (isOk)
                        {
                            return String.format(TEMPTABLEPAGERWITHIDENTITY, new Date().getTime(), topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy);
                        }
                    }
                    return String.format(TEMPTABLEPAGER, new Date().getTime(), pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy);
                case ACCESS:
                    if (!StringUtils.isEmpty(orderBy)) // 反转查询
                    {
                        return String.format(TOP4PAGER,columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy);
                    }
                    return String.format(TOP3PAGER, (rowCount - max > pageSize ? pageSize : rowCount - max),columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey));
                case SQLITE:
                case MYSQL:
                    if (max > 500000 && primaryKeyIsIdentity && String.valueOf(objWhere).equals(StringUtils.EMPTY) && !tableName.contains(" "))//单表大数量时的优化成主键访问。
                    {
                        where = String.format("%s>=(select %s from %s limit %s, 1) limit %s", primaryKey, tableName, max, pageSize);
                        return String.format(TOP1PAGER, columns, tableName, where);
                    }
                    return String.format(TOP1PAGER, columns, tableName, where + " limit " + pageSize + " offset " + max);
            }
		} while (dowhile);
        
        throw new CreaterException("Pager::No Be Support:" + dbType.toString());
    }
	
	
	public static String GetOrderBy(String where, boolean reverse, String primaryKey)
    {
        String orderby = " order by " + primaryKey + " ";

        int index = where.toLowerCase().indexOf("order by ");//order by XXasc , Xdesc desc
        if (index > -1)
        {
            if (where.toLowerCase().indexOf("asc") > -1 || where.toLowerCase().indexOf("desc") > -1)
            {
                where = where.substring(index);//不需要反转，直接取排序内容返回。
                if (!reverse)
                {
                    return where;
                }
                where = where.replace(",", " , ");
                String[] items = StringUtils.split(where,' ');
                orderby = StringUtils.EMPTY;
                for (String item : items)
                {
                    switch (item.toLowerCase())
                    {
                        case "asc":
                            orderby += "desc ";
                            break;
                        case "desc":
                            orderby += "asc ";
                            break;
                        default:
                            orderby += item + " ";
                            break;
                    }
                }
                return orderby.trim();
            }
            else if (reverse)
            {
                orderby = where + " desc";
            }
        }
        else
        {
            orderby += reverse ? "desc" : "asc";
        }
        return orderby.toLowerCase();
    }
	
	private static String ReverseOrderBy(String where, String primaryKey)
    {
        int index = where.toLowerCase().indexOf("order by ");
        if (index > -1)
        {
            String orderby = StringUtils.EMPTY;

            String onlyOrderBy = where.substring(index).trim();//order by t_sort,t_course_code desc,t_id "

            where = where.substring(0, index);
            onlyOrderBy = onlyOrderBy.replace(",", " , ");
            String[] items = StringUtils.split(onlyOrderBy,' ');
            orderby = StringUtils.EMPTY;
            boolean isAscOrDesc = false;
            for (String item : items)
            {
                switch (item.toLowerCase())
                {
                    case "asc":
                        orderby += "desc ";
                        isAscOrDesc = true;
                        break;
                    case "desc":
                        orderby += "asc ";
                        isAscOrDesc = true;
                        break;
                    case ","://遇上分号，如果前面没有升降序，默认为升，转为降。
                        if (!isAscOrDesc)
                        {
                            orderby += "desc";
                        }
                        orderby += item;
                        isAscOrDesc = false;
                        break;
                    case "":
                        break;
                    default:
                        orderby += item + " ";
                        isAscOrDesc = false;
                        break;
                }
            }
            String lastItem = items[items.length - 1].toLowerCase();
            if (lastItem != "asc" && lastItem != "desc") // 最后一项为空时，补desc
            {
                orderby += "desc";
            }
            return where + orderby.trim();

        }
        else
        {
            return where + " order by " + primaryKey + " desc";
        }

    }
}
